1. Field of the Invention
The present invention is directed to enhanced utilization of query optimization of a database system, particularly through substantially automatic maintenance of statistics for temporary tables.
2. Background Art
Computers are very powerful tools for storing and providing access to vast amounts of information. Databases are a common mechanism for storing information on computer systems while providing easy access to users. A typical relational database is an organized collection of related information stored as “records” having “fields” of information. As an example, a database of employees may have a record for each employee where each record contains fields designating specifics about the employee, such as name, home address, salary, and the like.
Between the actual physical database itself (i.e., the data actually stored on a storage device) and the users of the system, a database management system or DBMS is typically provided as a software cushion or layer. In essence, the DBMS shields the database user from knowing or even caring about the underlying hardware-level details.
One purpose of a database system is to answer queries requesting information from the database. A query may be defined as a logical expression over the data and the data relationships set forth in the database, and execution of a query results in the identification of a subset of the database. In operation, for instance, the execution of a request for information from a relational DBMS is typically issued by a client system as one or more Structured Query Language or “SQL” queries for retrieving particular data (e.g., a list of all employees earning more than $25,000) from database tables on a server. In response to this request, the database system typically returns the names of those employees earning $25,000, where “employees” is a table defined to include information about employees of a particular organization. The syntax of SQL is well documented, see e.g., “Information Technology—Database languages—SQL”, published by the American National Standards Institute as American National Standard ANSI/ISO/IEC 9075: 1992, the disclosure of which is hereby incorporated by reference.
SQL queries express what results are requested but do not state how the results should be obtained. In other words, the query itself does not tell how the query should be evaluated by the DBMS. Rather, a component of the DBMS called the optimizer determines the “plan” or the best method of accessing the data to implement the SQL query. The query optimizer is responsible for transforming a SQL request into an access plan composed of specific implementations of the algebraic operator selection, projection, join, and so forth. The role of a query optimizer in a relational DBMS system is to find an adequate execution plan from a search space of many semantically equivalent alternatives.
During typical query optimization, the optimizer chooses a plan which is estimated to be least costly. To achieve that goal, the optimizer typically relies on statistics about the tables, indexes, partitions, and columns referenced in a query for estimating the cost, usually in terms of I/O and CPU time of different possible query plans. The optimizer then chooses the query plan method that has the lowest cost. However, this cost estimate cannot be accurate if the statistics themselves are not accurate. As a result, inaccurate statistics could lead to a suboptimal choice of plans and result in slower performance than necessary. As optimizer capabilities become more complex in keeping with enhancements to database management systems, their choices increase, making statistics more important to help avoid generating a suboptimal query plan. Generally, it is the responsibility of the database administrator (DBA) to schedule and run an update statistics procedure. Unfortunately, reliance on a DBA is not optimal, since human nature may result in non-compliance, particularly for operations related to transient table use during database processing.
Accordingly, a need exists for an approach to providing enhanced utilization of query optimization that takes into account temporary table statistics. The present invention provides a solution for this and other needs.